package com.eleadmin.order.service.impl;

import cn.hutool.core.collection.CollUtil;
import com.eleadmin.common.core.config.ConfigProperties;
import com.eleadmin.common.core.config.WebSocketServer;
import com.eleadmin.damon.service.SequenceService;
import com.eleadmin.damon.utils.constant.UserConstant;
import com.eleadmin.damon.utils.results.PageResult2;
import com.eleadmin.damon.utils.utils.DateUtil;
import com.eleadmin.damon.utils.utils.PhoneUtils;
import com.eleadmin.damon.utils.utils.StringUtil;
import com.eleadmin.damon.utils.utils.Utils;
import com.eleadmin.common.core.utils.FileServerUtil;
import com.eleadmin.common.core.web.JsonResult;
import com.eleadmin.common.system.entity.Role;
import com.eleadmin.common.system.entity.User;
//import com.eleadmin.order.mapper.ContainerBillMapper;
import com.eleadmin.gps.param.CarpositionParam;
import com.eleadmin.gps.utils.MapNavUtil;
import com.eleadmin.gps.utils.ZjxlGps;
import com.eleadmin.order.biz.ContainerBillFeeBiz;
import com.eleadmin.order.biz.ContainerBillProcessBiz;
import com.eleadmin.order.biz.CustomerBiz33;
import com.eleadmin.order.entity.*;
import com.eleadmin.order.param.ContainerBillParam;
import com.eleadmin.order.result.ContainerBillWeekNum;
import com.eleadmin.order.service.ContainerBillService;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import com.eleadmin.order.biz.ContainerBillBiz;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.beans.factory.annotation.Autowired;
//import javax.transaction.Transactional;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 拖车运单服务实现类
 *
 * @author : Doman
 * @version ：1.0
 * @date : 2023-01-13 11:14:35
 */
@Service
public class ContainerBillServiceImpl /*extends ServiceImpl<ContainerBillMapper, ContainerBill> */ implements ContainerBillService {

    @Autowired
    private ContainerBillBiz containerBillBiz;
    @Autowired
    private CustomerBiz33 customerBiz;
    @Autowired
    private ContainerBillProcessBiz containerBillProcessBiz;
    @Autowired
    private ContainerBillFeeBiz containerBillFeeBiz;

    @Value("${runwithmodel}")
    public String runwithmodel;
//    public String runwithmodel = "prod";

    @Override
    public JsonResult list(PageResult2<T> page, ContainerBillParam containerBill, User loginUser) throws Exception {
//        WebSocketServer.sendInfo("1111","container");
//        WebSocketServer.sendInfo("22222","mapper");
        return containerBillBiz.list(page, containerBill, loginUser);
    }

    @Override
    public JsonResult list(ContainerBillParam containerBill, User loginUser) {
        return JsonResult.ok().setData(containerBillBiz.list(containerBill, loginUser));
    }

    @Override
    public JsonResult getById(String id) {
        return JsonResult.ok().setData(containerBillBiz.getById(id));
    }

    @Override
    public void carMapper() {
        webSocket();
    }

    public void webSocket() {
        List<ContainerBill> containerBills = containerBillBiz.carPositionList();
        if (containerBills != null && containerBills.size() > 0) {
            String vnos = "";
            for (ContainerBill c : containerBills) {
                vnos += c.getCarNo() + "_2,";
            }
            vnos = vnos.substring(0, vnos.length() - 1);
            CarpositionParam carpositionParam = new CarpositionParam();
            carpositionParam.setVnos(vnos);
            JSONArray ja = ZjxlGps.transTimeManageV3(carpositionParam, "prod");
//            JSONArray ja = ZjxlGps.transTimeManageV3(carpositionParam, runwithmodel);
            for (int i = 0; i < ja.size(); i++) {
                JSONObject obj = ja.getJSONObject(i);
                for (ContainerBill c : containerBills) {
                    if (obj.getString("vno").equals(c.getCarNo())) {
                        obj.put("id", c.getId());
                        obj.put("customer", c.getCustomer());
                        obj.put("billOfLading", c.getBillOfLading());
                        obj.put("driver", c.getDriver());
                        obj.put("driverPhone", c.getDriverPhone());
                        obj.put("factoryPackDate", c.getFactoryPackDate());
                        obj.put("factoryAddress", c.getFactoryAddress());
                        obj.put("carState", c.getState());
                        break;
                    }
                }
                if (!obj.getString("status").equals("1001")) {
                    obj.put("carState", obj.get("carState") + " 未获取实时位置");
                    continue;
                }
                Float lon = Float.valueOf(obj.getString("lon")) / 600000;
                obj.put("lon", lon);
                Float lat = Float.valueOf(obj.getString("lat")) / 600000;
                obj.put("lat", lat);
                obj.put("utc", DateUtil.timeStamp2Date(obj.getString("utc"), ""));
//                    carPosition.setUtc(DateUtil.timeStamp2Date(obj4.getString("utc"), ""));
                obj.remove("country");
                obj.remove("province");
                obj.remove("city");
            }
            WebSocketServer.sendInfo(ja.toString(), "mapper");
        }
    }


//    @Scheduled(cron = "0/30 * * * * *")
    public void carPosition() {
        if (runwithmodel.equals("prod")) {
            webSocket();
        }
    }


    @Transactional
    @Override
    public JsonResult order(ContainerBill containerBill, User loginUser) {
        Customer23123 customer = customerBiz.getById(containerBill.getCustomerId() + "");
        containerBill.setSalesmanid(customer.getSalesmanid());
        containerBill.setSalesman(customer.getSalesman());
        containerBill.setOperaterid(customer.getOperaterid());
        containerBill.setOperater(customer.getOperater());
        containerBill.setContacts(customer.getContacts());
        containerBill.setContactsPhone(customer.getContactsPhone());
        containerBill.setState("下单");
        Integer id = containerBillBiz.save(containerBill, loginUser);
        saveFeeByContainer(id, containerBill, loginUser);
        ContainerBillProcess process = new ContainerBillProcess(id + "", "下单");
        containerBillProcessBiz.save(process, loginUser);
        WebSocketServer.sendInfo(containerBill.getCustomer() + "已下单", "order");
        if (runwithmodel.equals("prod")) {
            String phone = "";
            //【宁波甩手掌柜网络科技有限公司】您好,客户:{1}, 提单号:{2}已下单,请您登陆甩手掌柜尽快接单,感谢您一直以来对甩手掌柜的支持,如有一问请联系客服(0574)28820591
            //郑诚18668575026 //林霞 187 13255844523  //王金轮 819 13968907228
            phone += "13255844523,18668575026";
//        if (containerBill.getSalesmanid().equals("78")) {
//            phone += ",13186850898";
//        }
//        User user = userService.getUserById(new User(Integer.valueOf(bill.getSalesmanId())));
//        if (phone.indexOf(user.getPhone()) == -1) {
//            phone += ","+user.getPhone();
//        }
            String regex = "^温州仓库|乐清湾|温州港$";
            //编译正则表达式
            Pattern pattern = Pattern.compile(regex);
            //检索匹配器对象
            Matcher matcher = pattern.matcher(containerBill.getDock());
            if (matcher.matches()) {
                phone += ",13968907228";
            }
            PhoneUtils.sendMsg(phone, "961275",
                    new String[]{containerBill.getCustomer(), containerBill.getBillOfLading() + ", 装箱时间:" + containerBill.getFactoryPackDate()}, 1);
        }
        return JsonResult.ok("添加成功");
    }

    public void saveFeeByContainer(Integer id, ContainerBill containerBill, User loginUser) {
        //应收
        ContainerBillFee containerBillFee = new ContainerBillFee();
        containerBillFee.setBillId(id + "");
        containerBillFee.setFeeName("条形码");
        containerBillFee.setType("应收");
        if (Integer.valueOf(containerBill.getBoxType().substring(0, 2)) > 20) {
            containerBillFee.setPrice("15");
        } else {
            containerBillFee.setPrice("10");
        }
        containerBillFee.setFeeModules("其他");
        containerBillFee.setFeeModulesOrder(400);
        Customer23123 customer = customerBiz.getById(containerBill.getCustomerId() + "");
        containerBillFee.setInvoicename(customer.getAbbreviation());
        containerBillFee.setInvoicestate(customer.getInvoiceState() + "");
        containerBillFee.setCurrency("CNY");
        containerBillFee.setExchangeRate("1");
        containerBillFeeBiz.save(containerBillFee, loginUser);

        containerBillFee = new ContainerBillFee();
        containerBillFee.setBillId(id + "");
        containerBillFee.setFeeName("运费");
        containerBillFee.setType("应收");
        containerBillFee.setPrice("1");
        containerBillFee.setFeeModules("运费");
        containerBillFee.setFeeModulesOrder(100);
        containerBillFee.setInvoicename(customer.getAbbreviation());
        containerBillFee.setInvoicestate(customer.getInvoiceState() + "");
        containerBillFee.setCurrency("CNY");
        containerBillFee.setExchangeRate("1");
        containerBillFeeBiz.save(containerBillFee, loginUser);

        containerBillFee = new ContainerBillFee();
        containerBillFee.setBillId(id + "");
        containerBillFee.setFeeName("吊机费");
        containerBillFee.setType("应收");
        if (Integer.valueOf(containerBill.getBoxType().substring(0, 2)) > 20) {
            containerBillFee.setPrice("30");
        } else {
            containerBillFee.setPrice("20");
        }
        containerBillFee.setFeeModules("其他");
        containerBillFee.setFeeModulesOrder(400);
        containerBillFee.setInvoicename(customer.getAbbreviation());
        containerBillFee.setInvoicestate(customer.getInvoiceState() + "");
        containerBillFee.setCurrency("CNY");
        containerBillFee.setExchangeRate("1");
        containerBillFeeBiz.save(containerBillFee, loginUser);

        //应付
        boolean wenzhou = false;
        for (Role role : loginUser.getRoles()) {
            if (role.getRoleName().equals("温州调度")) {
                wenzhou = true;
                break;
            }
        }
        if (wenzhou) {
            containerBillFee = new ContainerBillFee();
            containerBillFee.setBillId(id + "");
            containerBillFee.setFeeName("运费");
            containerBillFee.setType("应付");
            containerBillFee.setPrice("1");
            containerBillFee.setFeeModules("运费");
            containerBillFee.setFeeModulesOrder(100);
            //温州甩手
            customer = customerBiz.getById("30");
            containerBillFee.setInvoicename(customer.getAbbreviation());
            containerBillFee.setInvoicestate(customer.getInvoiceState() + "");
            containerBillFee.setCurrency("CNY");
            containerBillFee.setExchangeRate("1");
            containerBillFeeBiz.save(containerBillFee, loginUser);

            containerBillFee = new ContainerBillFee();
            containerBillFee.setBillId(id + "");
            containerBillFee.setFeeName("铅封费");
            containerBillFee.setType("应付");
            containerBillFee.setPrice("30");
            containerBillFee.setFeeModules("其他");
            containerBillFee.setFeeModulesOrder(400);
            //温州甩手
            customer = customerBiz.getById("30");
            containerBillFee.setInvoicename(customer.getAbbreviation());
            containerBillFee.setInvoicestate(customer.getInvoiceState() + "");
            containerBillFee.setCurrency("CNY");
            containerBillFee.setExchangeRate("1");
            containerBillFeeBiz.save(containerBillFee, loginUser);
        }


        containerBillFee = new ContainerBillFee();
        containerBillFee.setBillId(id + "");
        containerBillFee.setFeeName("吊机费");
        containerBillFee.setType("应付");
        containerBillFee.setPrice("120");
        containerBillFee.setFeeModules("其他");
        containerBillFee.setFeeModulesOrder(400);
        //易港通
        customer = customerBiz.getById("36");
        containerBillFee.setInvoicename(customer.getAbbreviation());
        containerBillFee.setInvoicestate(customer.getInvoiceState() + "");
        containerBillFee.setCurrency("CNY");
        containerBillFee.setExchangeRate("1");
        containerBillFeeBiz.save(containerBillFee, loginUser);
    }

    @Transactional
    @Override
    public JsonResult update(ContainerBill containerBill, User loginUser) {
        if (containerBillBiz.update(containerBill, loginUser)) {
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Transactional
    @Override
    public JsonResult remove(String id, User loginUser) {
        if (containerBillBiz.remove(id, loginUser)) {
            return JsonResult.ok("删除成功");
        }
        return JsonResult.error("删除失败");
    }

    @Transactional
    @Override
    public JsonResult updateState(ContainerBill containerBill, User loginUser) {
        if (containerBillBiz.updateState(containerBill, loginUser)) {
            ContainerBillProcess process = new ContainerBillProcess(containerBill.getId(), containerBill.getState());
            containerBillProcessBiz.save(process, loginUser);
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Transactional
    @Override
    public JsonResult finishState(ContainerBill containerBill, User loginUser) {
        JSONArray ja = JSONArray.fromObject(containerBill.getIds());
        List<String> arrayList = new ArrayList<String>();
        for (int i = 0; i < ja.size(); i++) {
            String id = ja.getJSONObject(i).getString("id");
            arrayList.add(id);

            ContainerBillProcess cbp = new ContainerBillProcess();
            cbp.setBillId(id);
            cbp.setState("结单");
            containerBillProcessBiz.save(cbp, loginUser);
        }
        ContainerBill cb = new ContainerBill();
        cb.setIdList(arrayList);
        cb.setState("结单");
        containerBillBiz.updateStateBatch(cb, loginUser);
        return JsonResult.ok("修改成功");
    }

    @Transactional
    @Override
    public JsonResult cash(ContainerBill containerBill, User loginUser) {
        if (containerBillBiz.cash(containerBill, loginUser)) {
            ContainerBillProcess process = new ContainerBillProcess(containerBill.getId(), "现金单");
            containerBillProcessBiz.save(process, loginUser);
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Transactional
    @Override
    public JsonResult dropBox(ContainerBill containerBill, User loginUser) {
        if (containerBillBiz.dropBox(containerBill, loginUser)) {
            ContainerBillProcess process = new ContainerBillProcess(containerBill.getId(), "落箱");
            containerBillProcessBiz.save(process, loginUser);
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Transactional
    @Override
    public JsonResult withholding(ContainerBill containerBill, User loginUser) {
        if (containerBillBiz.withholding(containerBill, loginUser)) {
            ContainerBillProcess process = new ContainerBillProcess(containerBill.getId(), "预提");
            containerBillProcessBiz.save(process, loginUser);
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Transactional
    @Override
    public JsonResult carTeam(ContainerBill containerBill, User loginUser) {
        containerBill.setState("指派车队");
        if (containerBillBiz.carTeam(containerBill, loginUser)) {
            ContainerBillProcess process = new ContainerBillProcess(containerBill.getId(), containerBill.getState());
            process.setRemark(containerBill.getCarTeam() + "" + containerBill.getDriver());
            containerBillProcessBiz.save(process, loginUser);
            return JsonResult.ok("指派车队成功");
        }
        return JsonResult.error("指派车队失败");
    }

    @Transactional
    @Override
    public JsonResult payReceivableVerify(ContainerBill containerBill, User loginUser) {
        if (containerBillBiz.updateState(containerBill, loginUser)) {
            ContainerBillProcess process = new ContainerBillProcess(containerBill.getId(), containerBill.getState());
            containerBillProcessBiz.save(process, loginUser);
//            List<ContainerBillProcess> list = containerBillProcessBiz.list(process, loginUser);
//            ContainerBillProcess process1 = null;
//            boolean ret = false, ret2 = false;
//            String data = containerBill.getState();
//            for (int i = 0; i < list.size(); i++) {
//                process1 = list.get(i);
//                if(process1.getState().equals("应付费用审核")) {
//                    ret = true;
//                }
//                if(process1.getState().equals("应收费用审核")) {
//                    ret2 = true;
//                }
//            }
//            if (ret && ret2) {
//                containerBill.setState("结单");
//                containerBillBiz.updateState(containerBill, loginUser);
//                process.setState("结单");
//                process.setCreateUser("1");
//                containerBillProcessBiz.save(process, loginUser);
//                data = "结单";
//            }
            return JsonResult.ok(process.getState());
        }
        return JsonResult.error("修改失败");
    }


    @Override
    public JsonResult getContainerOceanApplyBy(ContainOceanApply containOceanApply) {
        return JsonResult.ok().setData(containerBillBiz.getContainerOceanApplyBy(containOceanApply));
    }

    @Override
    public JsonResult saveContainerOceanApply(ContainOceanApply containOceanApply) {
        if ((containOceanApply.getContainbillId() == null || containOceanApply.getContainbillId().equals(""))
                && (containOceanApply.getOceanId() == null || containOceanApply.getOceanId().equals(""))
                && (containOceanApply.getApplyId() == null || containOceanApply.getApplyId().equals(""))
        ) {
            return JsonResult.ok("不能保存空项");
        }
        try {
            if (containOceanApply.getId() == null || containOceanApply.getId().equals("")) {
                String bh = "";
                containOceanApply.setBh(bh);
                containerBillBiz.saveContainerOceanApply(containOceanApply);
                return JsonResult.ok("保存成功");
            } else {
                containerBillBiz.updateContainerOceanApply(containOceanApply);
                return JsonResult.ok("关联修改成功");
            }
        } catch (DuplicateKeyException duplicateKeyException) {
            return JsonResult.error("已有绑定, 需要解绑后再绑定");
        }
    }

    @Override
    public JsonResult updateContainerOceanApply(ContainOceanApply containOceanApply) {
        containerBillBiz.updateContainerOceanApply(containOceanApply);
        return JsonResult.ok("保存成功");
    }

    @Override
    public JsonResult removeContainerOceanApply(ContainOceanApply containOceanApply) {
        if (containOceanApply.getId() == null || containOceanApply.getId().equals("")
        ) {
            return JsonResult.ok("不能解绑空项");
        }
        containerBillBiz.removeContainerOceanApply(containOceanApply);
        return JsonResult.ok("保存成功");
    }

    //应收应付分类导出财务报表
    @Override
    public JsonResult exportsFinance(ContainerBill containerBill, User loginUser, HttpServletResponse response, HttpServletRequest request) throws Exception {
        File file = new File(getUploadDir(), "finance.xlsx");
        if (file.exists()) {
            file.delete();
        }
        containerBill.setDate1(containerBill.getDate1().substring(0, 10) + " 00:00");
        containerBill.setDate2(containerBill.getDate2().substring(0, 10) + " 23:59");
        List<ContainerBill> containerBillList = getContainerList(containerBill, loginUser);
        if (containerBillList != null && containerBillList.size() > 0) {
            List<String> feeNames = containerBillFeeBiz.feeNames(containerBill, loginUser);
            if (feeNames != null && feeNames.size() > 0) {
                exportsFinanceContainerBillExcel(containerBillList, containerBill);
                FileServerUtil.preview(file, true, "finance.xlsx", null, null, response, request);
            } else {
                try {
                    response.setContentType("text/html; charset=UTF-8");
                    response.getWriter().println("没有对应的数据哦,请再次查询!");
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return JsonResult.ok("文件已导出");
    }


    public List<ContainerBill> getContainerList(ContainerBill containerBill, User loginUser) {
        ContainerBillParam containerBillParam = new ContainerBillParam();
        containerBillParam.setFactoryPackDateSearch1(containerBill.getDate1());
        containerBillParam.setFactoryPackDateSearch2(containerBill.getDate2());
        containerBillParam.setCustomerEq(containerBill.getCustomer());
        List<ContainerBill> containerBillList = containerBillBiz.list(containerBillParam, loginUser);
        if (containerBillList != null && containerBillList.size() > 0) {
            for (int i = 0; i < containerBillList.size(); i++) {
                ContainerBill containerBillTemp = containerBillList.get(i);
                containerBillParam = new ContainerBillParam();
//                if (StringUtil.isNotBlank(containerBill.getType())) {
//                    containerBillParam.setType(containerBill.getType());//导出应收应付
//                }
                if (StringUtil.isNotBlank(containerBillTemp.getId())) {
                    containerBillParam.setId(containerBillTemp.getId());
                }
                if (StringUtil.isNotBlank(containerBillTemp.getApplyId())) {
                    containerBillParam.setApplyId(containerBillTemp.getApplyId());
                }
//                if (StringUtil.isNotBlank(containerBill.getCustomerAbbreviation())) {
//                    containerBillParam.setCustomerAbbreviation(containerBill.getCustomerAbbreviation());
//                }
                List<ContainerBillFee> containerBillFeeList = containerBillFeeBiz.listForExport(containerBillParam, loginUser);
                containerBillTemp.setContainerBillFeeList(containerBillFeeList);
            }
        }
        return containerBillList;
    }

    public void exportsFinanceContainerBillExcel(List<ContainerBill> containerBillList, ContainerBill containerBill) throws Exception {
        //https://www.cnblogs.com/janson071/p/10119935.html
        //https://www.cnblogs.com/pejsidney/p/8383843.html
        //创建工作簿
        XSSFWorkbook xssfWorkbook = null;
        xssfWorkbook = new XSSFWorkbook();

        //创建工作表
        XSSFSheet xssfSheet;
        xssfSheet = xssfWorkbook.createSheet();
        xssfSheet.setForceFormulaRecalculation(true);//公式不自动执行的问题

        //创建行
        XSSFRow xssfRow;

        //创建列，即单元格Cell
        XSSFCell xssfCell;

        xssfRow = xssfSheet.createRow(0);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格*

        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 18); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容

        xssfRow = xssfSheet.createRow(1);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("Tel：0574-28820592"); //设置单元格内容

        xssfRow = xssfSheet.createRow(2);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String date2 = "";
        if (Utils.isNotEmpty(containerBill.getDate2())) {
            date2 = " 到 " + containerBill.getDate2().substring(0, 10);
        }
        xssfCell.setCellValue("账期：" + containerBill.getDate1().substring(0, 10) + date2); //设置单元格内容

        List<String> feeNamesys = new ArrayList<>();
        feeNamesys.add("运费");
        feeNamesys.add("提箱");
        feeNamesys.add("进港");
        feeNamesys.add("其他");
        feeNamesys.add("单项");
        feeNamesys.add("采购");
        feeNamesys.add("报关费");
        feeNamesys.add("易港通");
        feeNamesys.add("财务报销");
        List<String> feeNamesyf = new ArrayList<>();
        feeNamesyf.add("运费");
        feeNamesyf.add("提箱");
        feeNamesyf.add("进港");
        feeNamesyf.add("其他");
        feeNamesyf.add("单项");
        feeNamesyf.add("采购");
        feeNamesyf.add("报关费");
        feeNamesyf.add("易港通");
        feeNamesyf.add("财务报销");

        List<String> headRow = CollUtil.newArrayList("序号", "日期", "客户简称", "提单号", "箱型", "箱号", "封号", "工厂地址",
                "提箱场站", "还箱场站", "车队", "司机", "司机电话", "车牌号", "口岸", "加封地", "操作员", "业务员", "应收明细");
        headRow.addAll(feeNamesys);
        headRow.remove(headRow.size() - 1);
        headRow.add("应收合计");
        headRow.add("应付明细");
        headRow.addAll(feeNamesyf);
        headRow.remove(headRow.size() - 1);
        headRow.add("应付合计");
        headRow.add("应收不含税");
        headRow.add("应付不含税");
        headRow.add("折扣金额");
        headRow.add("操作费");
        headRow.add("利润");
        headRow.add("收款日期");
        xssfRow = xssfSheet.createRow(3);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色

        headRow = CollUtil.newArrayList("序号", "日期", "客户简称", "提单号", "箱型", "箱号", "封号", "工厂地址",
                "提箱场站", "还箱场站", "车队", "司机", "司机电话", "车牌号", "口岸", "加封地", "操作员", "业务员");
        headRow.addAll(feeNamesys);
        headRow.add("应收合计");
        headRow.addAll(feeNamesyf);
        headRow.add("应付合计");
        headRow.add("应收不含税");
        headRow.add("应付不含税");
        headRow.add("折扣金额");
        headRow.add("操作费");
        headRow.add("利润");
        headRow.add("收款日期");
        xssfRow = xssfSheet.createRow(4);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
//        xssfSheet.createFreezePane( 0, 5, 0, 0);//固定列

        String[] letters = new String[]{"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
                "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR",
                "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ",
                "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB",
                "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT",
                "CU", "CV", "CW", "CX", "CY", "CZ", "DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ", "DK", "DL",
                "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU", "DV", "DW", "DX", "DY", "DZ"};

        //把List里面的数据写到excel中
        for (int i = 0; i < containerBillList.size(); i++) {
            ContainerBill bill = containerBillList.get(i);
            List<ContainerBillFee> billFees = bill.getContainerBillFeeList();

            xssfRow = xssfSheet.createRow(i + 5);
            //创建每个单元格Cell，即列的数据
            xssfCell = xssfRow.createCell(0); //创建单元格
            xssfCell.setCellValue(i + 1); //设置单元格内容
            xssfCell = xssfRow.createCell(1); //创建单元格
            xssfCell.setCellValue(bill.getCustomer()); //设置单元格内容

            String add = "";
            if (bill.getFactoryAddress() != null && bill.getFactoryAddress().length() > 0) {
                JSONArray jsonArray = JSONArray.fromObject(bill.getFactoryAddress());
                for (int k=0; k<jsonArray.size(); k++) {
                    if(k != 0) {
                        add += ", \n";
                    }
                    JSONObject jsonObject = jsonArray.getJSONObject(k);
                    add += jsonObject.getString("factoryAddress").replaceAll("\t", "");
                }
            }

            List<String> row = CollUtil.newArrayList((i + 1) + "", DateUtil.formatDate(DateUtil.parseDate(bill.getFactoryPackDate() + ":00"), "MM-dd"),
                    bill.getCustomer(), bill.getBillOfLading(), bill.getBoxType(),
                    bill.getBoxNum(), bill.getSealno(), add,
                    bill.getUpYard() == null ? "/" : bill.getUpYard(), bill.getArrivalPort() == null ? "/" : bill.getArrivalPort(),
                    bill.getCarTeam(), bill.getDriver(), bill.getDriverPhone(), bill.getCarNo(),
                    bill.getDock(), bill.getSealedLand(), bill.getOperater(), bill.getSalesman()
            );
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setBold(false); // 加粗
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);

//            float temp_ret = 0;
//            for(int t = 0; t < feeNamesys.size(); t ++) {
//                String feeName = feeNamesys.get(t);
//                temp_ret = 0;
//                for (int j = 0; j < billFees.size(); j ++) {
//                    BillFee bf = billFees.get(j);
//                    if(bf.getType().equals("应收") && bf.getFeeName().equals(feeName)) {
//                        if(Utils.isNotEmpty(bf.getPrice())) {
//                            temp_ret += Float.parseFloat(bf.getPrice());
//                        }
//                    }
//                }
//                row.add(temp_ret + "");
//            }
            float temp_ret = 0;
            for (int t = 0; t < feeNamesys.size(); t++) {
                String feeName = feeNamesys.get(t);
                temp_ret = 0;
                for (int j = 0; j < billFees.size(); j++) {
                    ContainerBillFee bf = billFees.get(j);
                    if (bf.getType().equals("应收") && bf.getFeeModules().equals(feeName)) {
                        if (Utils.isNotEmpty(bf.getPrice())) {
                            temp_ret += Float.parseFloat(bf.getPrice());
                        }
                    }
                }
                row.add(temp_ret + "");
            }
            row.add("function::SUM(" + letters[8] + (i + 6) + ":" + letters[feeNamesys.size() + 7] + (i + 6) + ")");

//            float cwbx = 0,ygt = 0;
//            for(int t = 0; t < feeNamesyf.size(); t ++) {
//                cwbx = 0;
//                ygt = 0;
//                temp_ret = 0;
//                String feeName = feeNamesyf.get(t);
//                for (int j = 0; j < billFees.size(); j ++) {
//                    BillFee bf = billFees.get(j);
//                    if(Utils.isNotEmpty(bf.getPrice())) {
//                        if (bf.getType().equals("应付")) {
//                            if (bf.getFeeName().equals(feeName) && !bf.getInvoicename().equals("易港通")
//                                    && !bf.getInvoicename().equals("财务报销")) {
//                                temp_ret += Float.parseFloat(bf.getPrice());
//                            } else if (bf.getInvoicename().equals("易港通")) {
//                                ygt += Float.parseFloat(bf.getPrice());
//                            } else if (bf.getInvoicename().equals("财务报销")) {
//                                cwbx += Float.parseFloat(bf.getPrice());
//                            }
//                        }
//                    }
//                }
//                row.add(temp_ret + "");
//            }
//            row.add(cwbx+"");
//            row.add(ygt+"");
            for (int t = 0; t < feeNamesyf.size(); t++) {
                temp_ret = 0;
                String feeName = feeNamesyf.get(t);
                for (int j = 0; j < billFees.size(); j++) {
                    ContainerBillFee bf = billFees.get(j);

                    if (bf.getType().equals("应付") && bf.getFeeModules().equals(feeName)) {
                        if (Utils.isNotEmpty(bf.getPrice())) {
                            temp_ret += Float.parseFloat(bf.getPrice());
                        }
                    }
                }
                row.add(temp_ret + "");
            }
            row.add("function::SUM(" + letters[feeNamesys.size() + 9] + (i + 6) + ":" + letters[feeNamesys.size() + feeNamesyf.size() + 8] + (i + 6) + ")");
            float ysTotalNoTax = 0, yfTotalNoTax = 0;
            for (ContainerBillFee bf2 : billFees) {
                if (Utils.isNotEmpty(bf2.getPrice())) {
                    if (bf2.getType().equals("应收")) {
                        float cc = Float.parseFloat(bf2.getPrice());
                        ysTotalNoTax += cc;
                    } else {
                        float cc = Float.parseFloat(bf2.getPrice());
                        yfTotalNoTax += cc;
                    }
                }
            }
            row.add(Utils.twoDot(ysTotalNoTax));
            row.add(Utils.twoDot(yfTotalNoTax));
            int profitIndex = feeNamesys.size() + feeNamesyf.size() + 6;
            row.add("function::TRUNC(IMSUB(IMSUB(IMSUB(" + letters[profitIndex + 3] + (i + 6) + "," + letters[profitIndex] + (i + 6) + ")," + letters[profitIndex + 1] + (i + 6) + ")," + letters[profitIndex + 2] + (i + 6) + "),2)");
            row.add("10");
//            row.add(Utils.twoDot(ysTotalNoTax-yfTotalNoTax-10)+"");
            profitIndex = feeNamesys.size() + feeNamesyf.size() + 10;
            row.add("function::TRUNC(IMSUB(IMSUB(" + letters[profitIndex] + (i + 6) + "," + letters[profitIndex + 1] + (i + 6) + ")," + letters[profitIndex + 3] + (i + 6) + "),2)");
            row.add("");
            String numColumns = ",";
            for (int t = 0; t < feeNamesys.size() + feeNamesys.size() + 8; t++) {
                numColumns += (t + 18) + ",";
            }
            setRow(xssfRow, row, cellStyle, numColumns);
        }

        // 合并单元格
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 23 + feeNamesys.size() + feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(1, 1, 0, 23 + feeNamesys.size() + feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(2, 2, 0, 23 + feeNamesys.size() + feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(3, 3, 18, 17 + feeNamesys.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(3, 3, 19 + feeNamesys.size(), 18 + feeNamesys.size() + feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        for (int i = 0; i <= 25 + feeNamesys.size() + feeNamesyf.size(); i++) {
            if (i < 18 || i == (18 + feeNamesys.size()) || i > (18 + feeNamesys.size() + feeNamesyf.size())) {
                cra = new CellRangeAddress(3, 4, i, i); // 起始行, 终止行, 起始列, 终止列
                xssfSheet.addMergedRegion(cra);
                RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
                RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
            }
        }

        xssfRow = xssfSheet.createRow(5 + containerBillList.size());
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("合计:"); //设置单元格内容

        for (int i = 0; i <= feeNamesys.size() + feeNamesyf.size() + 6; i++) {
            xssfCell = xssfRow.createCell(18 + i); //创建单元格
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellType(CellType.NUMERIC);
            xssfCell.setCellFormula("SUM(" + letters[i + 8] + "6:" + letters[i + 8] + (containerBillList.size() + 5) + ")"); //设置单元格内容
        }
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(20 + feeNamesys.size() + feeNamesyf.size() + 5); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue(""); //设置单元格内容

        cra = new CellRangeAddress(5 + containerBillList.size(), 5 + containerBillList.size(), 0, 17); // 起始行, 终止行, 起始列, 终止列
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
        xssfSheet.addMergedRegion(cra);

        OutputStream os = new FileOutputStream(new File(getUploadDir() + "finance.xlsx"));
        //用输出流写到excel
        xssfWorkbook.write(os);
        os.flush();
        os.close();
    }


    public void setRow(XSSFRow xssfRow, List<String> row, XSSFCellStyle cellStyle, String changeNum) {
        //创建每个单元格Cell，即列的数据
        if (Utils.isNotEmpty(row)) {
            for (int i = 0; i < row.size(); i++) {
                XSSFCell xssfCell = xssfRow.createCell(i); //创建单元格
                String str = row.get(i);
                if (str == null) {
                    str = "";
                }
                //https://blog.csdn.net/weixin_29100399/article/details/114203311
                if (str.indexOf("function::") != -1) {
                    xssfCell.setCellFormula(str.substring(10, str.length())); //设置单元格内容
                } else if (changeNum.indexOf("," + i + ",") != -1) {
                    xssfCell.setCellType(CellType.NUMERIC);
                    if (Utils.isNotEmpty(row.get(i))) {
                        xssfCell.setCellValue(Double.valueOf(row.get(i))); //设置单元格内容
                    } else {
                        xssfCell.setCellValue(0);
                    }
                } else {
                    xssfCell.setCellValue(row.get(i)); //设置单元格内容
                }
                if (cellStyle != null) {
                    xssfCell.setCellStyle(cellStyle);
                }
            }
        }
    }

    @Resource
    private ConfigProperties config;

    /**
     * 文件上传基目录
     */
    private String getUploadBaseDir() {
        return File.listRoots()[config.getUploadLocation()].getAbsolutePath()
                .replace("\\", "/") + "/upload/";
    }

    /**
     * 文件上传位置
     */
    private String getUploadDir() {
        return getUploadBaseDir() + "file/";
    }


    @Override
    public JsonResult listDayNum(ContainerBill containerBill) {
        return JsonResult.ok().setData(containerBillBiz.listDayNum(containerBill));
    }

    @Override
    public JsonResult listMonthNum(ContainerBill containerBill) {
        return JsonResult.ok().setData(containerBillBiz.listMonthNum(containerBill));
    }

    @Override
    public JsonResult listDockNum(ContainerBill containerBill) {
        if (containerBill.getDock().equals("month") || containerBill.getDock().equals("last month")
                || containerBill.getDock().equals("quarter") || containerBill.getDock().equals("last quarter")) {
            List<ContainerBillWeekNum> billWeekNums = containerBillBiz.listDockNum(containerBill);
            if (billWeekNums != null && billWeekNums.size() > 0) {
                for (ContainerBillWeekNum bwn : billWeekNums) {
                    JSONArray ja = JSONArray.fromObject(bwn.getFactoryAddress());
                    JSONObject jb = ja.getJSONObject(0);
                    try {
                        MapNavUtil mapResult = new MapNavUtil(jb.getString("latitude") + "," + jb.getString("longitude"), UserConstant.GAODE_MAP_KEY);
                        bwn.setFactoryAddress(mapResult.getTxAddress().getAddress().replaceAll("�", ""));
                    } catch (Exception e) {
                        bwn.setFactoryAddress(jb.getString("factoryAddress"));
                    }
                }
            }
            return JsonResult.ok().put("data", billWeekNums);
        }
        return JsonResult.ok().put("data", containerBillBiz.listDockNum(containerBill));
    }

    @Override
    public JsonResult listCustomerAndDriverNum(ContainerBill containerBill) {
        return JsonResult.ok().setData(containerBillBiz.listCustomerAndDriverNum(containerBill));
    }

    @Override
    public JsonResult calendarBillNum(ContainerBill containerBill) {
        return JsonResult.ok().setData(containerBillBiz.calendarBillNum(containerBill));
    }
}
